/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */

package solucionessgh.Util;


import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import javax.swing.JOptionPane;
import solucionessgh.View.ModelProject;

/**
 *
 * @author roderick
 */
public class DataBase {
    
    private String url = ModelProject.Instance().getName("url");
    private String user = ModelProject.Instance().getName("user");
    private String pass = ModelProject.Instance().getName("pass");
    
    public DataBase(){
        
    }

    protected  Connection ConnectionBase(){
        try{
            Class.forName("org.postgresql.Driver");
            Connection con = DriverManager.getConnection( url,user,pass);
            return con;
        }
        catch( Exception e ){
            JOptionPane.showMessageDialog(null,
                e.getMessage(),
                "Nivel Usuario",
                JOptionPane.ERROR_MESSAGE);
        }
        return null;
    }

    protected  ResultSet find(String query){

        //System.out.println(query);
        ResultSet rs = null;
        try{
            Class.forName("org.postgresql.Driver");
            Connection con = DriverManager.getConnection( url,user,pass);
            Statement stmt = con.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,ResultSet.CONCUR_READ_ONLY);
            rs = stmt.executeQuery(query);
            return rs;
        }
        catch( Exception e ){
            JOptionPane.showMessageDialog(null,
                e.getMessage(),
                "Nivel Usuario",
                JOptionPane.ERROR_MESSAGE);
        }
        return null;
    }

    protected boolean execute(String query){
        String url = "jdbc:postgresql://localhost/sgh";
        try{
            Class.forName("org.postgresql.Driver");
            Connection con = DriverManager.getConnection( url,"postgres","postgres");
            Statement stmt = con.createStatement();
            stmt.executeQuery(query);
            return true;
        }
        catch( Exception e ){
            JOptionPane.showMessageDialog(null,
                e.getMessage(),
                "Nivel Usuario",
                JOptionPane.ERROR_MESSAGE);
        }
        return false;
    }

    public ArrayList ejecutaQuery(String query) {
    ArrayList alResultado = new ArrayList();
    Connection conBBDD = null;
    Statement st = null;
    ResultSet rs = null;
    try {
        conBBDD=ConnectionBase();
        st = conBBDD.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,ResultSet.CONCUR_READ_ONLY);
        rs = st.executeQuery(query);
        int cont = 1;

        ResultSetMetaData md = rs.getMetaData();
        int numeroColumnas = md.getColumnCount();
        ArrayList alRegistro = new ArrayList(numeroColumnas);


        /*for (int i = 1; i <= numeroColumnas; i++) {
            String nomCol = md.getColumnName(i);
            alRegistro.add(nomCol);
        }

        alResultado.add(alRegistro);*/


            while (rs.next() ) {
                alRegistro = new ArrayList();
                for (int i = 1; i <= numeroColumnas; i++) {
                    alRegistro.add(rs.getString(i));
                }

                alResultado.add(alRegistro);
                cont++;

            }

       Integer numTuplasTotal = new Integer(0);
        if (rs.last()) { // Existen tuplas y el cursor esta en la ultima,
                            // basta con consultar el numero de esa tupla
            numTuplasTotal = new Integer(rs.getRow());
        }
        alResultado.add(0, numTuplasTotal);
    } catch (Exception e) {
        e.printStackTrace();
    } finally {
        if(st!=null) {
            try {
                st.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        if(rs!=null) {
            try {
                rs.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        if(conBBDD!=null) {
            try {
                conBBDD.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }

        }
    }

    return (alResultado);

    }
}
